package db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import db.Table.Column;


public class Database {

	private static Connection connection = null;
	
	public static Connection getConnection() throws SQLException{
		if(connection == null){
			connection = DriverManager.getConnection("jdbc:hsqldb:file:db/bot;shutdown=true", "SA", "");
		}
		return connection;
	}
	
	public static Statement getStatement() throws SQLException{
		return getConnection().createStatement();
	}
	
	public static PreparedStatement getPreparedStatement(String sql) throws SQLException{
		return getConnection().prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
	}
	
	public static void disconnect() throws SQLException{
		if(connection != null){
			connection.commit();
			connection.close();
			connection = null;
		}
	}
	
	protected static boolean existsTable(String name){
		boolean result = false;
		try {
			PreparedStatement statement = Database.getPreparedStatement("SELECT COUNT(\"TABLE_NAME\") FROM \"INFORMATION_SCHEMA\".\"SYSTEM_TABLES\" WHERE \"TABLE_SCHEM\" = 'PUBLIC' AND \"TABLE_NAME\" = ?;");
			statement.setString(1, name);
			ResultSet resultSet = statement.executeQuery();
			resultSet.next();
			result = (resultSet.getInt(1) == 1);
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	protected static void createTable(String name, String properties, Column... columns){
		String sql = "CREATE TABLE \""+name+"\" (";
		for(Column column : columns){
			sql += column+", ";
		}
		if((properties == null) || (properties == "")){
			sql = sql.substring(0, sql.length()-2);
		}else{
			sql += properties;
		}
		sql += ");";
		System.out.println(sql);
		try {
			Statement statement = Database.getStatement();
			statement.execute(sql);
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void createDataBase(){
		if (!existsTable("INVESTIGADOR")){
			createTable("INVESTIGADOR", null,
					new Column("ID","INTEGER","IDENTITY PRIMARY KEY"),
					new Column("ID_CONICET","INTEGER","NOT NULL"),
					new Column("NOMBRE","VARCHAR(256)","NOT NULL"),
					new Column("CATEGORIA","VARCHAR(256)"),
					new Column("DISCIPLINA_CIENTIFICA","VARCHAR(256)"),
					new Column("DISCIPLINA_DESAGREGADA","VARCHAR(256)"),
					new Column("CAMPO_DE_APLICACION","VARCHAR(256)"),
					new Column("ESPECIALIDAD","VARCHAR(256)"),
					new Column("AREA","VARCHAR(256)"),
					new Column("DIRECTOR","INTEGER"),
					new Column("CODIRECTOR","INTEGER"));
		}
		if (!existsTable("ARTICULO")){
			createTable("ARTICULO", null,
					new Column("ID","INTEGER","IDENTITY PRIMARY KEY"),
					new Column("ID_CONICET","INTEGER"),
					new Column("TITULO","VARCHAR(256)","NOT NULL"),
					new Column("REVISTA","VARCHAR(256)"),
					new Column("EDITORIAL","VARCHAR(256)"),
					new Column("REFERENCIAS","VARCHAR(256)"),
					new Column("ISN","VARCHAR(256)"),
					new Column("RESUMEN","VARCHAR(8192)"));
		}
		if (!existsTable("INVESTIGADOR_ARTICULO")){
			createTable("INVESTIGADOR_ARTICULO", null,
					new Column("ID_INVESTIGADOR","INTEGER","NOT NULL"),
					new Column("ID_ARTICULO","INTEGER","NOT NULL"));
		}
		if (!existsTable("LUGAR_DE_TRABAJO")){
			createTable("LUGAR_DE_TRABAJO", null,
					new Column("ID_INVESTIGADOR","INTEGER","NOT NULL"),
					new Column("LUGAR_DE_TRABAJO","VARCHAR(256)","NOT NULL"));
		}
		if (!existsTable("ALIAS")){
			createTable("ALIAS", null,
					new Column("ID_INVESTIGADOR","INTEGER","NOT NULL"),
					new Column("ALIAS","VARCHAR(256)","NOT NULL"));
		}
		if (!existsTable("TITULO")){
			createTable("TITULO", null,
					new Column("ID_INVESTIGADOR","INTEGER","NOT NULL"),
					new Column("TITULO","VARCHAR(256)","NOT NULL"));
		}
	}
}